from django.http import HttpResponse
from openpyxl.reader.excel import load_workbook
from rest_framework.viewsets import ModelViewSet
from rest_framework.decorators import action
from . import serializers,models
from openpyxl import Workbook
from openpyxl.styles import Border, Side, Font, Alignment, PatternFill
import pandas as pd
from io import BytesIO

# Create your views here.

class BookInfoListView(ModelViewSet):
    queryset = models.BookInfo.objects.all()
    serializer_class = serializers.BookInfoSerializer

    @action(methods=['get'],detail=False)
    def output_excel(self,reqeust):
        # 获取所有的对象并序列化
        queryset = self.get_queryset()
        serializer = self.get_serializer(queryset,many=True)

        # 将序列化后的数据转换为DataFrame
        df = pd.DataFrame(serializer.data)

        #获取模型的Meta类中的verbose_name作为列标题
        filed_names = [field.verbose_name for field in models.BookInfo._meta.fields]
        print(filed_names)

        #重写配置表头
        df.columns = filed_names

        # 创建一个BytesIO对象来保存Excel文件
        output = BytesIO()

        # 创建一个Excel writer对象，使用openpyxl作为引擎，并指定输出流
        excel_writer = pd.ExcelWriter(output, engine='openpyxl')

        df.to_excel(excel_writer,index=False)
        excel_writer.close()

        workbook = load_workbook(filename=output, read_only=False)

        # 选择活动的工作表
        worksheet = workbook.active

        # 定义边框样式
        thin_border = Border(left=Side(style='thin'),
                             right=Side(style='thin'),
                             top=Side(style='thin'),
                             bottom=Side(style='thin'))
        # 定义表头字体样式（加粗）
        header_font = Font(bold=True)

        # 定义表头对齐方式（居中）
        header_alignment = Alignment(horizontal='center', vertical='center')

        # 定义表头填充颜色（蓝色）
        header_fill = PatternFill(start_color='ADD8E6',
                                  end_color='ADD8E6',
                                  fill_type='solid')

        # 外边框（用于表头）
        outer_border = Border(left=Side(style='thin'),
                              right=Side(style='thin'),
                              top=Side(style='thin'),
                              bottom=Side(style='thin'))

        # 内外边框（用于非表头单元格）
        inner_border = Border(left=Side(style='thin'),
                              right=Side(style='thin'),
                              top=Side(style='thin'),
                              bottom=Side(style='thin'),
                              diagonal=Side(style='thin'),  # 可选，用于单元格之间的连接
                              diagonal_direction=0)  # 方向，0表示无方向

        # 应用边框到所有单元格
        for row_index, row in enumerate(worksheet.iter_rows(), 1):
            for cell in row:
                if row_index == 1:  # 如果是表头行
                    cell.border = outer_border  # 只应用外边框
                else:
                    cell.border = inner_border  # 应用内外边框

        # 确保表头单元格之间没有内部边框
        for col in worksheet.iter_cols(min_row=1, max_row=1):
            for i in range(len(col) - 1):
                col[i].border = Border(top=col[i].border.top,
                                       left=col[i].border.left,
                                       right=Side(style=None),  # 移除右边框
                                       bottom=Side(style=None))  # 移除底边框
            # 对于最后一列，确保没有右边框
            col[-1].border = Border(top=col[-1].border.top,
                                    left=col[-1].border.left,
                                    right=Side(style=None),
                                    bottom=col[-1].border.bottom)


        # 应用样式到表头
        for cell in worksheet[1]:  # 假设表头在第一行
            cell.font = header_font
            cell.alignment = header_alignment
            cell.fill = header_fill

        # 重新保存工作簿到输出流
        workbook.save(output)

        # 将文件指针移动到开始位置
        output.seek(0)

        response = HttpResponse(output.read(),
                                content_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",status=200)
        response['Content-Disposition'] = 'attachment; filename=books.xlsx'
        return response